package cn.levi.excel.utils;

import cn.hutool.core.annotation.AnnotationUtil;
import cn.hutool.core.bean.BeanUtil;
import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.img.FontUtil;
import cn.hutool.core.io.FileUtil;
import cn.hutool.core.lang.Assert;
import cn.hutool.core.lang.Dict;
import cn.hutool.core.map.MapUtil;
import cn.hutool.core.util.ArrayUtil;
import cn.hutool.core.util.EnumUtil;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.ReflectUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.core.util.ZipUtil;
import cn.hutool.extra.expression.ExpressionUtil;
import cn.hutool.extra.spring.SpringUtil;
import cn.hutool.json.JSON;
import cn.hutool.json.JSONUtil;
import cn.hutool.poi.excel.ExcelFileUtil;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import cn.hutool.poi.excel.RowUtil;
import cn.hutool.system.SystemUtil;
import cn.levi.excel.ExcellDictContextHolder;
import cn.levi.excel.annotation.Excell;
import cn.levi.excel.annotation.ExcellValidate;
import cn.levi.excel.configuration.ExcellProperties;
import cn.levi.excel.exceptions.DictNotFoundException;
import cn.levi.excel.exceptions.ExcelException;
import cn.levi.excel.exceptions.MethodNotFoundException;
import cn.levi.excel.module.ImportBo;
import cn.levi.excel.module.ImportRow;
import cn.levi.excel.module.StateBo;
import cn.levi.excel.validate.ValidateDate;
import cn.levi.excel.validate.ValidateExcelImport;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.Comparator;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.function.Function;
import java.util.stream.Collectors;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Comment;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.DataValidationHelper;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.springframework.web.multipart.MultipartFile;

/**
 * excell操作工具类，该类是以注解{@link Excell}(标注）和{@link ExcellValidate}（校验）为操作基础的工具类<br/>
 * 主要功能包括，<br/>
 * 1.下载模板。<br/>
 * 2.导入数据，<br/>
 * 3.生成错误数据清单，<br/>
 * 4.导出数据
 * @author lei
 * @since 2024/9/18
 */

@Slf4j
public class ExcellUtils {

	/**
	 * excel导入的最大文件限制.限制最大50MB
	 */
	public static final Integer MAX_FILE = 10485760*5;

	/**
	 * 导入的行数限制
	 */
	public static final Integer MAX_ROW = Integer.MAX_VALUE;

	/**
	 * 根据code获取枚举的描述的方法
	 */
	private static final String[] METHOD = {"getNameByValue","getNameByCode"};

	/**
	 * 需要忽略的错误类型,当用户操作execel时存在一些业务允许的异常需要用户看到
	 */
	private static Class<? extends Exception>[] ignoreExceptionClass;
	static {
		ExcellProperties configuration = SpringUtil.getBean(ExcellProperties.class);
		ExcellUtils.ignoreExceptionClass =configuration==null?new Class[0]: configuration.getIgnoreExceptionCs();
	}
	/**
	 * 添加一个下拉行
	 * @param hssfWorkbook  工作簿
	 * @param arr           需要下拉的数据
	 * @param rowNum        开始行
	 * @param colNum        列
	 */
	public static void writeDataOne(Workbook hssfWorkbook, Collection<String> arr,int rowNum, int colNum){
		Sheet sheetOne = hssfWorkbook.getSheetAt(0); // 获取第一个Sheet
		// 创建下拉列表的区域，这里是第一行的第一列  CellRangeAddressList(开始行，终止行，开始列，终止列)
		CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(rowNum, 65535, colNum, colNum);
		// 创建下拉列表数据
		DataValidationHelper helper = sheetOne.getDataValidationHelper();
		DataValidationConstraint constraint = helper.createExplicitListConstraint(arr.toArray(new String[0]));
		// 应用下拉列表
		DataValidation dataValidation = helper.createValidation(constraint, cellRangeAddressList);
		sheetOne.addValidationData(dataValidation);
	}
	/**
	 * 在导入文件的基础上进行移除正常数据，并将错误数据写入 ExcelWriter
	 * 如果没有错误数据则返回空
	 *
	 * @param reader 读者
	 * @param datas  数据
	 * @return
	 */
	public static <T> ExcelWriter buildErrExcellWriter(ExcelReader reader, List<ImportRow<T>> datas,Class<T> beanClass) {
		boolean hasFail = datas.stream().anyMatch(f -> !f.getSuccess());
        //没有失败的，全部成功则不需要生成错误文件
		if (!hasFail) {
			return null;
		}
		//计算列总数
		long columns = Arrays.stream(ReflectUtil.getFields(beanClass)).filter(f -> {
			Excell annotation = AnnotationUtil.getAnnotation(f, Excell.class);
			return annotation != null && !annotation.ignore();
		}).count();
		int columnInts = Integer.parseInt(String.valueOf(columns));
		//采用倒序的形式删除，防止错位
		Sheet sheet = reader.getSheet();
		ExcelWriter writer = new ExcelWriter(reader.getWorkbook(), sheet.getSheetName());
		//编写错误信息表头
		writer.writeCellValue(columnInts,0, "失败原因");

		for (int i = datas.size() - 1; i >= 0; i--) {
			ImportRow<T> row = datas.get(i);
			if (row.getSuccess()) {
				RowUtil.removeRow(sheet.getRow(row.getRowNum()));
			}else {
				//将错误信息书写到每行的末尾
				writer.writeCellValue(columnInts,row.getRowNum(), row.getReason());
				writer.setRowHeight( row.getRowNum(), 32);
			}
		}
		//一个汉字大概占宽度为2个字符（english）的宽度
		int maxErrMsgLength = datas.stream().filter(Objects::nonNull).map(ImportRow::getReason).filter(Objects::nonNull).max(Comparator.comparingInt(String::length)).orElse("").length();
		writer.setColumnWidth(columnInts, maxErrMsgLength * 2);
		return writer;
	}

	/**
	 * 设置标题样式
	 *
	 * @param writer         作家
	 * @param x              x
	 * @param y              y
	 * @param fondColorIndex 颜色指数
	 * @return
	 */
	public static void setHeaderStyle(ExcelWriter writer, int x, int y,short fondColorIndex,int width,int height) {
		CellStyle cellStyle = writer.createCellStyle(x, y);
		setCenter(cellStyle);
		writer.setColumnWidth(x, width);
		writer.setRowHeight(y, height);
		Font font = writer.createFont();
		font.setFontName("微软雅黑");
		font.setColor(fondColorIndex);
//		font.setBold(true);
		font.setFontHeightInPoints((short) 11);
		cellStyle.setFont(font);
		setBorder(cellStyle);
		// 填充前景色(两个一起使用)
		cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.index);
		cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		writer.getCell(x, y).setCellStyle(cellStyle);
	}

	/**
	 * 设置单元格样式
	 *
	 * @param writer         作家
	 * @param x              x
	 * @param y              y
	 * @param fondColorIndex 颜色指数
	 * @return
	 */
	public static CellStyle setCellStyle(ExcelWriter writer, int x, int y, short fondColorIndex, int width, int height) {
		CellStyle cellStyle = writer.createCellStyle(x, y);
		setCenter(cellStyle);
		writer.setColumnWidth(x, width);
		writer.setRowHeight(y, height);
		Font font = writer.createFont();
		font.setFontName("微软雅黑");
		font.setColor(fondColorIndex);
		//		font.setBold(true);
		font.setFontHeightInPoints((short) 11);
		cellStyle.setFont(font);
		setBorder(cellStyle);
		return cellStyle;
	}

	/**
	 * 设置边界
	 *
	 * @param cellStyle 单元格样式
	 * @return
	 */
	public static CellStyle setBorder(CellStyle cellStyle) {
		// 顶边栏
		cellStyle.setBorderTop(BorderStyle.THIN);
		cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
		// 右边栏
		cellStyle.setBorderRight(BorderStyle.THIN);
		cellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
		// 底边栏
		cellStyle.setBorderBottom(BorderStyle.THIN);
		cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
		// 左边栏
		cellStyle.setBorderLeft(BorderStyle.THIN);
		cellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
		return cellStyle;
	}

	/**
	 * 设定中心
	 *
	 * @param cellStyle 单元格样式
	 * @return
	 */
	public static CellStyle setCenter(CellStyle cellStyle) {
		cellStyle.setAlignment(HorizontalAlignment.CENTER);
		cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
		return cellStyle;
	}
	/**
	 * 转化为对应的实体，字典和枚举自动映射为编码
	 *
	 *
	 * @param map     数据来源
	 * @param targetClss 目标cls
	 * @return
	 */
	private static  <T> T convertEntity(Map<String, Object> map,Class<T> targetClss) {
		if (MapUtil.isEmpty(map)) {
			return null;
		}
		Field[] fields = ReflectUtil.getFields(targetClss);
		//过滤掉要忽略的字段
		List<Field> excellFields = Arrays.stream(fields).filter(f -> {
			Excell annotation = AnnotationUtil.getAnnotation(f, Excell.class);
			return annotation != null && !annotation.ignore();
		}).collect(Collectors.toList());
		T target = ReflectUtil.newInstanceIfPossible(targetClss);
		for (Field field : excellFields) {
			Object fieldValue = map.get(field.getName());
			if (ObjectUtil.isNull(fieldValue)) {
				continue;
			}
			Excell annotation = AnnotationUtil.getAnnotation(field, Excell.class);
			String typeCode = annotation.typeCode();
			if (StrUtil.isNotBlank(typeCode)&& ObjectUtil.isNotNull(fieldValue) ) {
				Dict dict = getDictContextHolder().getDict(typeCode);
				if (!dict.containsValue(fieldValue)) {
					throw new DictNotFoundException(StrUtil.format("{}填写不符合格式", annotation.value()));
				}
				dict.forEach((k,v)->{
					if (v.toString().equals(fieldValue)) {
						ReflectUtil.setFieldValue(target,field.getName(),k);
					}
				});
				continue;
			}
			//枚举
			Class<? extends Enum<?>> enu = annotation.enu();
			if (!enu.equals(Excell.Enu.class)) {
				List<Object> codes = EnumUtil.getFieldValues(enu, "code");
				List<Object> names = EnumUtil.getFieldValues(enu, "name");
				if (!names.contains(fieldValue)) {
					throw new DictNotFoundException(StrUtil.format("{}填写不符合格式", annotation.value()));
				}
				Object enumCodeValue = codes.get(names.indexOf(fieldValue));
				ReflectUtil.setFieldValue(target,field.getName(),enumCodeValue);
				continue;
			}
			//日期 LocalDate
			if (field.getType().getTypeName().equals(LocalDate.class.getTypeName())) {
				ReflectUtil.setFieldValue(target, field.getName(), ValidateDate.parseLocalDateTime(fieldValue.toString()));
			}
			//日期 LocalDateTime
			if (field.getType().getTypeName().equals(LocalDateTime.class.getTypeName())) {
				ReflectUtil.setFieldValue(target, field.getName(), ValidateDate.parseLocalDateTime(fieldValue.toString()));
			}
			if (ReflectUtil.hasField(targetClss, field.getName())) {
				ReflectUtil.setFieldValue(target,field.getName(),fieldValue);
			}
		}
		return target;
	}

	/**
	 * 获取dict上下文保持器,<br/>
	 * 此处应该返回一个错误，提醒调用者去提供对应的字典查询实现类<br/>
	 * 但是考虑到可能大多数的调用场景下，都不会使用到字典数据进行转化，所以此处提供默认空实现，
	 *
	 * @return
	 */
	private static ExcellDictContextHolder getDictContextHolder(){
		return SpringUtil.getBean(ExcellDictContextHolder.class);
	}

	/**
	 * 获取标头别名
	 *
	 * @param param param
	 * @return
	 */
	private static Map<String,String> getHeaderAlias(Class param){
		HashMap<String, String> res = new HashMap<>();
		Field[] fields = ReflectUtil.getFields(param);
		for (Field field : fields) {
			Excell annotation = field.getAnnotation(Excell.class);
			boolean required = annotation.required();
			//这里设置别名，当存在必填时，可能存在‘*列名’的情况，所以两种都加上
			if (required) {
				res.put("*" + annotation.value(), field.getName());
			}
			res.put(annotation.value() , field.getName());
		}
		return res;

	}
	/**
	 * 解析excell数据映射为响应的实体列表
	 * @param reader      文件
	 * @param entityClass 实体类
	 * @param startRow    起始行
	 * @param maxRows     限制导入的最大行数
	 * @return
	 */
	public static <T> List<ImportRow<T>> parseExcel(ExcelReader reader,Class<T> entityClass,int startRow,int maxRows){
		reader.setIgnoreEmptyRow(true);
		//提取数据
		int rowCount = reader.getRowCount();
		if (rowCount > maxRows) {
			throw new ExcelException(StrUtil.format("导入数据超过{}行，请分批导入！",maxRows));
		}
		reader.setHeaderAlias(getHeaderAlias(entityClass));
		reader.setCellEditor((cell, value) -> {
			//避免空串
			if (ObjectUtil.isEmpty(value)) {
				return null;
			}
			//全部转化为string
			return StrUtil.trim(value.toString());
		});
		//标题行
		int headerIndex = startRow - 1;
		List<Map<String, Object>> sources = reader.read(headerIndex,startRow,Integer.MAX_VALUE);
		List<ImportRow<T>> rows = new ArrayList<>();
		int currentRow = startRow;
		for (Map<String, Object> source : sources) {
			ImportRow<T> row = new ImportRow<>();
			//先走校验 再进行转换，记录校验结果
			StateBo stateBo = validateField(source, entityClass);
			row.setSuccess(stateBo.getSuccess());
			row.setReason(stateBo.getReason());
			row.setRowNum(currentRow++);
			row.setSource((LinkedHashMap<String, Object>) source);
			if (stateBo.getSuccess()) {
				//转换为实体，字典枚举自动映射
				T target = null;
				try {
					target = convertEntity(source, entityClass);
				} catch (ExcelException e) {
					row.setSuccess(false);
					row.setReason(e.getMessage());
					stateBo.setSuccess(false);
					stateBo.setReason(e.getMessage());
				}catch (Exception e) {
					log.error("解析excel数据行失败，失败原因{}",e.getMessage(),e);
					row.setSuccess(false);
					row.setReason("解析失败");
					stateBo.setSuccess(false);
					stateBo.setReason("解析失败");
				}
				row.setData(target);
			}
			rows.add(row);
		}
		return rows;
	}
	/**
	 * 对解析后的数据执行回调导入，返回执行结果
	 *
	 * @param importRows 解析校验后的excel数据。
	 * @param action     解析后的数据处理操作，比如更新，入库
	 * @return
	 */
	private static <T> ImportBo importEntities(List<ImportRow<T>> importRows, Function<T, Boolean> action) {
		ImportBo importBo = new ImportBo();
		//执行自定义操作
		int success = importRows
				.stream()
				//过滤出校验通过的数据
				.filter(ImportRow::getSuccess)
				//执行自定义回调逻辑
				.map(f -> callbackPack(f, action))
				//将返回值成功的转化为数字方便计算
				.mapToInt(f -> f ? 1 : 0)
				//统计回调成功的数据
				.reduce(0, Integer::sum);
		importBo.setSuccessNum(success);
		importBo.setFailNum(importRows.size() - success);
		return importBo;
	}

	/**
	 * 次操作用于对rowData数据进行自定义处理的包装，
	 *
	 * @param rowData 行数据
	 * @param action  调用者行为
	 * @return
	 */
	private static <T> boolean callbackPack(ImportRow<T> rowData, Function<T, Boolean> action) {
		try {
			Boolean apply = action.apply(rowData.getData());
			if (!apply){
				rowData.setSuccess(false);
				rowData.setReason("导入失败！");
				return false;
			}
			return true;
		} catch (ExcelException e) {
			rowData.setSuccess(false);
			rowData.setReason(e.getMessage());
			return false;
		}catch (Exception e) {
			log.error("导入数据错误，错误原因：{}",e.getMessage(),e);
			rowData.setSuccess(false);
			if (Arrays.stream(ignoreExceptionClass).anyMatch(f->e.getClass().isAssignableFrom(f))) {
				rowData.setReason(e.getMessage());
				return false;
			}
			rowData.setReason("导入失败！");
			return false;
		}
	}
	/**
	 * 获取excelReader处理器
	 *
	 * @param file 文件
	 * @return
	 */
	private static ExcelReader getExcelReader(MultipartFile file) {
		//校验文件
		validateFileXlsx(file);
		ExcelReader reader = null;
		try (InputStream inputStream = file.getInputStream()) {
			reader = ExcelUtil.getReader(inputStream, 0);
		} catch (IOException e) {
			throw new RuntimeException(e);
		}
		return reader;
	}
	/**
	 * 导入excell数据，负责将excell数据映射为响应的实体列表
	 *
	 * @param file          文件
	 * @param entityClass   实体类
	 * @param parseCallback 校验和转化完成后的用户自定义操作
	 * @param startRowIndex 开始解析的起始行索引
	 * @param maxRows       限制导入的最大行数
	 * @return
	 */
	public static <T> ImportBo importExcell(MultipartFile file, Class<T> entityClass,int startRowIndex, int maxRows, Function<T, Boolean> parseCallback) {
		//解析文件
		ExcelReader reader = getExcelReader(file);
		return importExcellReader(reader, entityClass, startRowIndex, maxRows,parseCallback);
	}
	/**
	 * 导入excell数据，负责将excell数据映射为响应的实体列表,并在返回值中填充错误文件
	 *
	 * @param file          文件
	 * @param entityClass   实体类
	 * @param parseCallback 校验和转化完成后的用户自定义操作
	 * @param startRowIndex 开始解析的起始行索引
	 * @param maxRows       限制导入的最大行数
	 * @return
	 */
	public static <T> ImportBo importExcellFeedErrFile(MultipartFile file, Class<T> entityClass,int startRowIndex, int maxRows, Function<T, Boolean> parseCallback) {
		//解析文件
		ExcelReader reader = getExcelReader(file);
		return importExcellReaderFeedErrFile(reader, entityClass,  startRowIndex, maxRows, parseCallback);
	}
	/**
	 * 导入excell数据，负责将excell数据映射为响应的实体列表
	 *
	 * @param reader          excel reader
	 * @param entityClass   实体类
	 * @param parseCallback 校验和转化完成后的用户自定义操作
	 * @param startRowIndex 开始解析的起始行索引
	 * @param maxRows       限制导入的最大行数
	 * @return
	 */
	public static <T> ImportBo importExcellReader(ExcelReader reader, Class<T> entityClass,int startRowIndex, int maxRows, Function<T, Boolean> parseCallback) {
		//解析为实体类（包含校验结果和原始数据）
		List<ImportRow<T>> importRows= parseExcel(reader, entityClass,startRowIndex, maxRows);
		//导入
		ImportBo importBo = importEntities(importRows, parseCallback);
		//构建异常信息
		String errInfo = buildErrInfo(importRows);
		importBo.setErrorMsg(errInfo);
		return importBo;
	}
	/**
	 * 导入excell数据，负责将excell数据映射为响应的实体列表,并在返回值中填充错误文件
	 *
	 * @param reader           excel reader
	 * @param entityClass   实体类
	 * @param parseCallback 校验和转化完成后的用户自定义操作
	 * @param startRowIndex 开始解析的起始行索引
	 * @param maxRows       限制导入的最大行数
	 * @return
	 */
	public static <T> ImportBo importExcellReaderFeedErrFile(ExcelReader reader, Class<T> entityClass,int startRowIndex, int maxRows,Function<T, Boolean> parseCallback) {
		//解析为实体类（包含校验结果和原始数据）
		List<ImportRow<T>> importRows= parseExcel(reader, entityClass,startRowIndex, maxRows);
		//导入
		ImportBo importBo = importEntities(importRows, parseCallback);
		//构建异常信息
		String errInfo = buildErrInfo(importRows);
		importBo.setErrorMsg(errInfo);
		//构建异常文件
		ExcelWriter writer = buildErrExcellWriter(reader, importRows, entityClass);
		importBo.setFailExcel(writer);
		//返回导入结果
		return importBo;
	}

	/**
	 * 生成异常信息
	 *
	 * @param importRows 导入行
	 * @return
	 */
	private static <T> String buildErrInfo(List<ImportRow<T>> importRows) {
		if (CollectionUtil.isEmpty(importRows)) {
			return StrUtil.EMPTY;
		}
		//过多的错误数据没有意义，只取前面一百条
		return importRows.stream()
				//找到错误的
				.filter(f->!f.getSuccess())
				//只取前面100条，过多错误信息有啥意义，用户会一个一个去看？
				.limit(100)
				//构建错误信息
				.map(ExcellUtils::buildRowErrInfo)
				.collect(Collectors.joining("\n"));
	}

	/**
	 * 生成行错误信息
	 *
	 * @param importRows 导入行
	 * @return
	 */
	private static <T> String buildRowErrInfo(ImportRow<T> importRows) {
		if (importRows.getSuccess()) {
			return StrUtil.EMPTY;
		}
		return StrUtil.concat(true,
				"第",
				importRows.getRowNum().toString(),
				"行：",
				importRows.getReason());
	}

	/**
	 * 验证文件xlsx<br/>
	 * 1.文件飞空<br/>
	 * 2.文件格式判断<br/>
	 * 3.文件大小限制<br/>
	 *
	 * @param file 文件
	 * @return
	 */
	@SneakyThrows
	private static void validateFileXlsx(MultipartFile file) {
		Assert.notNull(file, () -> new ExcelException("文件不能为空"));
		InputStream is = file.getInputStream();
		Assert.isTrue(ExcelFileUtil.isXlsx(is)||ExcelFileUtil.isXls(is), () -> new ExcelException("文件过大（不能超过50MB），请分批导入"));
		Assert.isFalse(file.getSize() > MAX_FILE, () -> new ExcelException("文件过大（不能超过50MB），请分批导入"));
	}
	/**
	 * 快速验证（当存在一个验证为通过时，立刻返回验证结果）验证一行数据字段是否合法。</br>
	 * 更具beanClass上的注解{@link ExcellValidate#validator()}中的策略进行校验，该注解依赖{@link Excell}
	 * @param rowData 行数据,从excel中读取到的一行数据
	 * @param beanClass 转换的实体，需要字段上添加注解{@link ExcellValidate}和{@link Excell}
	 * @return 对该行数据的校验结果
	 */
	private static <T> StateBo  validateField(Map<String,Object> rowData,Class<T> beanClass ) {
		Field[] fields = ReflectUtil.getFields(beanClass);
		for (Field field : fields) {
			if (!AnnotationUtil.hasAnnotation(field, ExcellValidate.class)) {
				continue;
			}
			ExcellValidate excellValidate = AnnotationUtil.getAnnotation(field, ExcellValidate.class);
			Excell excell = AnnotationUtil.getAnnotation(field, Excell.class);
			if (excell == null) {
				throw new ExcelException("@ExcellValidate要和@Excell注解联合使用");
			}
			if (!excellValidate.enable()) {
				continue;
			}
			//自定义校验器
			Class<? extends ValidateExcelImport>[] validator = excellValidate.validator();
			//验证是否通过校验
			for (Class<? extends ValidateExcelImport> validateItem : validator) {
				ValidateExcelImport validateExcelImport = SpringUtil.getBean(validateItem);
				StateBo stateBo = validateExcelImport.validate(field, rowData);
				if (!stateBo.getSuccess()) {
					return stateBo;
				}
			}
		}
		return StateBo.success();
	}
	/**
	 * 在指定的sheet创建excel模板
	 *
	 * @param entityClass 实体类
	 * @return
	 */
	public static <T> ExcelWriter creatExcelTemplateSheet(Class<T> entityClass,String sheetName) {
		ExcelWriter writer = ExcelUtil.getWriter(true);
		writer.setSheet(sheetName);
		writer.setRowHeight(-1, 32);
		int startRow = 0,startColl=0;
		return creatExcelTemplate(writer,entityClass, startRow, startColl);
	}

	/**
	 * 获取类及其所有父类的字段，按继承顺序排列
	 * @param clazz 目标类
	 * @return 按父类到子类顺序排列的字段列表
	 */
	public static List<Field> getFieldsInHierarchyOrder(Class<?> clazz) {
		List<Class<?>> classHierarchy = new ArrayList<>();
		List<Field> result = new ArrayList<>();

		// 收集类继承链（从父类到子类）
		Class<?> current = clazz;
		while (current != null && current != Object.class) {
			classHierarchy.add(0, current); // 插入到开头，形成父类->子类顺序
			current = current.getSuperclass();
		}

		// 按继承顺序收集字段
		for (Class<?> c : classHierarchy) {
			Field[] fields = c.getDeclaredFields();
			result.addAll(Arrays.asList(fields));
		}
		return result;
	}

	/**
	 * 创建excel模板 由调用方负责关闭流
	 * 常规的表头模板
	 *
	 * @param entityClass 实体类
	 * @return
	 */
	public static <T> ExcelWriter creatExcelTemplate(ExcelWriter writer,Class<T> entityClass,int startRow,int startColl) {
		//找出包含Ecell注解并且不被忽略的
		List<Field> fields = filterAndSortField(entityClass);
		//字段重新排序，从父类开始往后排序，
		writer.setRowHeight(-1, 32);
		for (Field field : fields) {
			Excell annotation = field.getAnnotation(Excell.class);
			boolean required = annotation.required();
			String value = annotation.value();
			writer.writeCellValue(startColl, startRow,required? "*"+value:value);
			String typeCode = annotation.typeCode();
			Class<? extends Enum<?>> enu = annotation.enu();

			int height = annotation.height();
			int with = annotation.with();
			String notes = annotation.notes();
			//样式设置
			ExcellUtils.setHeaderStyle(writer, startColl, startRow, required ? IndexedColors.RED.getIndex() : IndexedColors.BLACK.getIndex(), with, height);
			//处理字典
			if (!enu.equals(Excell.Enu.class)) {
				Collection<String> enums = convertEnum(enu);
				ExcellUtils.writeDataOne(writer.getWorkbook(),enums,startRow+1, startColl);
			}
			if (!Objects.equals(typeCode, "")) {
				Collection<String> dict = convertDict(typeCode);
				ExcellUtils.writeDataOne(writer.getWorkbook(),dict,startRow+1, startColl);
			}
			//设置注释
			if (StrUtil.isNotBlank(notes)) {
				Drawing<?> drawingPatriarch = writer.getSheet().createDrawingPatriarch();
				Comment comment = drawingPatriarch.createCellComment(new XSSFClientAnchor());
				comment.setString(new XSSFRichTextString(notes));
				writer.getCell(startColl, startRow).setCellComment(comment);
			}
			startColl++;
		}
		return writer;
	}

	private static <T> List<Field> filterAndSortField(Class<T> entityClass) {
		List<Field> fields = getFieldsInHierarchyOrder(entityClass).stream()
				// 过滤掉忽略的字段
				.filter(f -> {
					Excell annotation = AnnotationUtil.getAnnotation(f, Excell.class);
					return annotation != null && !annotation.ignore();
				})
				//按sort排序
				.sorted(Comparator.comparing(f->{
					Excell annotation = AnnotationUtil.getAnnotation(f, Excell.class);
					return annotation.sort();
				}))
				.collect(Collectors.toList());
		return fields;
	}

	/**
	 * 创建excel模板，
	 * 模板会有表格中的大标题
	 * 由调用方负责关闭流
	 *
	 * @param entityClass 实体类
	 * @param title       标题
	 * @param titleEndRowIndex   标题行止的索引
	 * @return
	 */
	public static <T> ExcelWriter creatExcelTemplate(Class<T> entityClass,String title,int titleEndRowIndex) {
		Field[] fields = ReflectUtil.getFields(entityClass);
		ExcelWriter writer = ExcelUtil.getWriter(true);
		writer.setRowHeight(-1, 32);
		int startRow = 0,startColl=0;
		if (StrUtil.isNotBlank(title)) {
			//表头
			writer.merge(0, titleEndRowIndex, 0, fields.length-1, title, true);
			writer.setRowHeight(0, 32);
			startRow = 1;
		}
		return creatExcelTemplate(writer,entityClass, startRow, startColl);
	}

	/**
	 * 创建excel模板 由调用方负责关闭流
	 *
	 * @param entityClass 实体类
	 * @return
	 */
	public static <T> ExcelWriter creatExcelTemplate(Class<T> entityClass) {
		ExcelWriter writer = ExcelUtil.getWriter(true);
		writer.setRowHeight(-1, 32);
		int startRow = 0,startColl=0;
		return creatExcelTemplate(writer,entityClass, startRow, startColl);
	}
	/**
	 * 导出数据 处理字典转换,时间格式设置等
	 *
	 * @param entityClass 实体类
	 * @param datas       数据
	 * @return
	 */
	public static <T> void export(ExcelWriter writer,Class<T> entityClass,List<T> datas){
		if (CollectionUtil.isEmpty(datas)) {
			return ;
		}
		//换行
		writer.setCurrentRowToEnd();
		int row = writer.getCurrentRow();

		//找出包含Ecell注解并且不被忽略的
		List<Field> fields = filterAndSortField(entityClass);
		for (T data : datas) {
			int column = 0;

			for (Field field : fields) {
				Object value = ReflectUtil.getFieldValue(data,field);
				Excell excell = AnnotationUtil.getAnnotation(field, Excell.class);
				if (value == null) {
					// 写一下值，保证单元格样式一致性
					writer.writeCellValue(column++, row, null);
					continue;
				}
				//枚举
				if (AnnotationUtil.hasAnnotation(field, Excell.class)) {
					Class<? extends Enum<?>> enu = excell.enu();
					if (!Excell.Enu.class.equals(enu)) {
						//获取对应枚举值
						value = getEnuName(value, enu);
					}
					String typeCode = excell.typeCode();
					if (StrUtil.isNotBlank(typeCode)) {
						//获取对应字典值
						value = getDictContextHolder().getDict(typeCode, StrUtil.toString(value));
					}
				}
				writer.writeCellValue(column, row, value);
				//单元格格式设置
				String dataFormat = excell.dataFormat();
				if (StrUtil.isNotBlank(dataFormat)) {
					CellStyle cellStyle = writer.createCellStyle(column, row);
					setBorder(cellStyle);
					setCenter(cellStyle);
					cellStyle.setDataFormat(writer.getWorkbook().createDataFormat().getFormat(dataFormat));
					writer.getCell(column,row).setCellStyle(cellStyle);
				}
				// 单元格样式计算
				String fontColorEl = excell.fontColor();
				if (StrUtil.isNotBlank(fontColorEl)) {
					//将数据data转化为map,转化后是
					Object res = ExpressionUtil.eval(fontColorEl, BeanUtil.beanToMap(data));
					CellStyle cellStyle = writer.getCell(column, row).getCellStyle();
					if (res!=null){
						Font font = writer.getWorkbook().createFont();
						font.setFontName("宋体");
						font.setColor(Short.parseShort(res.toString()));
						cellStyle.setFont(font);
					}
					writer.getCell(column,row).setCellStyle(cellStyle);
				}
				String backColorEl = excell.backColor();

				if (StrUtil.isNotBlank(backColorEl)) {
					//将数据data转化为map,转化后是
					Object res = ExpressionUtil.eval(backColorEl, BeanUtil.beanToMap(data));
					CellStyle cellStyle = writer.getCell(column, row).getCellStyle();
					if (res!=null){
						cellStyle.setFillForegroundColor(Short.parseShort(res.toString()));
						cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
					}
					writer.getCell(column,row).setCellStyle(cellStyle);
				}
				column++;
			}
			writer.setRowHeight(row++, 32);
		}
	}

	/**
	 * 从json设置单元格样式
	 *
	 * @param cellStyle 单元格样式
	 * @param font      字体
	 * @param json      JSON
	 * @return
	 */
	private static CellStyle setCellStyleFromJson(CellStyle cellStyle, Font font,String json) {
		//先检查格式是否正确
		if (!JSONUtil.isTypeJSONObject(json)) {
			throw new IllegalArgumentException("参数错误，单元格样式el表达式必须为类似:\n {\n" + "\t\"font\": {\n" + "\t\t\"color\": 23,\n" + "\t\t\"fontName\": \"微软雅黑\",\n" + "\t\t\"fontSize\": 22\n" + "\t},\n" + "\t\"background\": {\n" + "\t\t\"color\": 23\n" + "\t}\n" + "}");
		}
		//解析成json
		JSON setting = JSONUtil.parse(json);

		//设置字体
		Object color = setting.getByPath("font.color");
		if (color != null) {
			font.setColor((short) color);
		}
		Object fontName = setting.getByPath("font.fontName");
		if (fontName != null) {
			font.setFontName(fontName.toString());
		}
		Object fontSize = setting.getByPath("font.fontSize");
		if (fontSize != null) {
			font.setFontHeightInPoints((short) fontSize);
		}
		//设置背景
		Object backColor = setting.getByPath("background.color");
		if (backColor != null) {
			cellStyle.setFillForegroundColor((short) backColor);
			cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		}

		return cellStyle;
	}

	/**
	 * 导出分表打包成zip
	 *
	 * @param writers      作家
	 * @param zipName      zip名称
	 * @param itemNameFunc 项目名称func
	 * @return
	 */
	public static File exportZip(List<ExcelWriter> writers,String zipName,Function<Integer,String> itemNameFunc){
		List<File> res = new ArrayList<>();
		String tmpdir = SystemUtil.get(SystemUtil.TMPDIR);
		for (int i = 0; i < writers.size(); i++) {
			ExcelWriter writer = writers.get(i);
			//重命名
			String itemName = itemNameFunc.apply(i + 1);
			itemName = itemName.split("\\.")[0]+".xlsx";
			File file = new File(tmpdir,itemName );
			writer.flush(file);
			res.add(file);
		}
		//将这些数据打包成Zip文件
		File file = new File(tmpdir, zipName.split("\\.")[0]+".zip");
		ZipUtil.zip(file, false, ArrayUtil.toArray(res,File.class));
		res.forEach(FileUtil::del);
		return file;
	}

	/**
	 * 导出excel或zip
	 *
	 * @param excelLimit 每页的数据量限制
	 * @param datas      数据
	 * @param fileName   文件名
	 * @param tClass     T类
	 * @return
	 */
	public static <T> File exportExcelOrZip(int excelLimit, List<T> datas, String fileName, Class<T> tClass) {
		return exportExcelOrZip(excelLimit, datas, fileName, f -> fileName.split("\\.")[0] + "-" + f + ".xlsx", tClass);
	}

	/**
	 * 导出excel或zip
	 *
	 * @param excelLimit   每页的数据量限制
	 * @param datas        数据
	 * @param fileName     文件名
	 * @param itemNameFunc zip包中的excel名称规则
	 * @param tClass       T类
	 * @return
	 */
	public static <T> File exportExcelOrZip(int excelLimit, List<T> datas, String fileName, Function<Integer, String> itemNameFunc, Class<T> tClass) {
		//确定数据是否超出限制
		int total = datas.size();
		if (total <= excelLimit) {
			//返回excel
			ExcelWriter export = ExcellUtils.export(tClass, datas);
			String temp = SystemUtil.get(SystemUtil.TMPDIR);
			File file = FileUtil.file(temp, fileName.endsWith(".xlsx") ? fileName : fileName + ".xlsx");
			export.flush(file);
			return file;
		}
		//确定excel数量
		int excelCount = (total + excelLimit - 1) / excelLimit;
		List<ExcelWriter> res = new ArrayList<>();
		for (int i = 0; i < excelCount; i++) {
			//分割出列表
			List<T> itemList = datas.subList(i * excelLimit, Math.min((i + 1) * excelLimit, total));
			res.add(ExcellUtils.export(tClass, itemList));
		}
		//返回zip
		return exportZip(res, fileName.endsWith(".zip") ? fileName : fileName + ".zip", itemNameFunc);
	}

	/**
	 * 导出Zip
	 *
	 * @param writers 作家
	 * @param zipName zip名称
	 * @return
	 */
	public static File exportZip(List<ExcelWriter> writers,String zipName){
		return exportZip(writers, zipName, f -> zipName.split("\\.")[0] + "-" + f + ".xlsx");
	}

	/**
	 * 获取枚举的name
	 *
	 * @param value code
	 * @param et    et
	 * @return
	 */
	public static Object getEnuName(Object value, Class<? extends Enum<?>> et) {
		//根据code 获取枚举描述的方法 获取枚举的方法
		Method method = null;
		for (String name : METHOD) {
			method = ReflectUtil.getMethod(et, name, Integer.class);
			if (method!=null)
			{
				break;
			}
		}
		if (method==null) {
			log.error("枚举：{}未提供获取对应描述的方法，请提供方法：{}", et.getName(),METHOD);
			throw new MethodNotFoundException("方法未找到!");
		}
		method.setAccessible(true);
		return ReflectUtil.invokeStatic(method, value);
	}
	/**
	 * 导出数据 处理字典转换,时间格式设置等
	 *
	 * @param entityClass 实体类
	 * @param datas       数据
	 * @return
	 */
	public static <T> ExcelWriter export(Class<T> entityClass,List<T> datas) {
		ExcelWriter writer = creatExcelTemplate(entityClass);
		export(writer, entityClass, datas);
		return writer;
	}

	/**
	 * 导出数据 支持表头标题设置
	 *
	 * @param entityClass 实体类
	 * @param datas       数据
	 * @param title       标题
	 * @param titleEndRow 标题末尾行
	 * @return
	 */
	public static <T> ExcelWriter export(Class<T> entityClass, List<T> datas, String title, int titleEndRow) {
		ExcelWriter writer = creatExcelTemplate(entityClass, title, titleEndRow);
		export(writer, entityClass, datas);
		return writer;
	}


	/**
	 * 转换枚举
	 *
	 * @param typeCode 类型代码
	 * @return
	 */
	private static Collection<String> convertDict(String typeCode) {
		Dict dict = getDictContextHolder().getDict(typeCode);
		return dict.values().stream().map(String::valueOf).collect(Collectors.toList());

	}

	/**
	 * 转换字典
	 *
	 * @param enu ENU
	 * @return
	 */
	private static Collection<String> convertEnum(Class<? extends Enum<?>> enu) {
		return EnumUtil.getFieldValues(enu, "name").stream().map(String::valueOf).collect(Collectors.toList());
	}
}
